﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.VisualBasic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace QueryData;

public class BasicDbContext : DbContext
{
    // 把显式编译的查询保存到私有变量
    private static Func<BasicDbContext, int, IEnumerable<BasicEntity>> _getGreatThan = EF.CompileQuery((BasicDbContext db, int id) => db.BasicEntities.Where(x => x.Id > id).ToList());

    public DbSet<BasicEntity> BasicEntities => Set<BasicEntity>();
    public DbSet<MainEntity> MainEntities => Set<MainEntity>();
    public DbSet<SubEntity> SubEntities => Set<SubEntity>();
    public DbSet<SubSubEntity> SubSubEntities => Set<SubSubEntity>();

    public BasicDbContext()
    {

    }

    public BasicDbContext(DbContextOptions options) : base(options)
    {

    }

    public BasicDbContext(DbContextOptions<BasicDbContext> options) : base(options)
    {

    }

    // 通过公共方法引用编译的查询
    public IEnumerable<BasicEntity> GetGreatThan(int id)
    {
        return _getGreatThan(this, id);
    }

    // 引用表值函数并暴露到IQueryable中
    public IQueryable<FunTableEntity> GetFunTable(int number) => FromExpression(() => GetFunTable(number));

    // 定义映射到自定义EF Core SQL翻译的方法，因此方法本身没有实现，如果需要同时支持客户端调用的话也可以提供实现
    [DbFunction]
    public static double MyFunc(double first, int second) => throw new NotImplementedException();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseSqlServer();

        // 启用延迟加载代理
        optionsBuilder .UseLazyLoadingProxies ();

        // 注册命令拦截器
        optionsBuilder.AddInterceptors(new MyCommandInterceptor());

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // 注册表值函数的实体类型
        modelBuilder.Entity<FunTableEntity>().HasNoKey();

        // 注册表值函数，数据库中也要定义匹配的函数
        modelBuilder.HasDbFunction(() => GetFunTable(default))
            .HasName("FunTable");

        // 注册自定义翻译表达式，手动构建表达式树
        // 100 * ABS(first - second) / ((first + second) / 2)
        modelBuilder.HasDbFunction(
                typeof(BasicDbContext).GetMethod(nameof(MyFunc)))
            .HasTranslation(
                args =>
                    new SqlBinaryExpression(
                        ExpressionType.Multiply,
                        new SqlConstantExpression(
                            Expression.Constant(100),
                            new IntTypeMapping("int", DbType.Int32)),
                        new SqlBinaryExpression(
                            ExpressionType.Divide,
                            new SqlFunctionExpression(
                                "ABS",
                                new SqlExpression[]
                                {
                                    new SqlBinaryExpression(
                                        ExpressionType.Subtract,
                                        args.First(),
                                        args.Skip(1).First(),
                                        args.First().Type,
                                        args.First().TypeMapping)
                                },
                                nullable: true,
                                argumentsPropagateNullability: new[] { true, true },
                                type: args.First().Type,
                                typeMapping: args.First().TypeMapping),
                            new SqlBinaryExpression(
                                ExpressionType.Divide,
                                new SqlBinaryExpression(
                                    ExpressionType.Add,
                                    args.First(),
                                    args.Skip(1).First(),
                                    args.First().Type,
                                    args.First().TypeMapping),
                                new SqlConstantExpression(
                                    Expression.Constant(2),
                                    new IntTypeMapping("int", DbType.Int32)),
                                args.First().Type,
                                args.First().TypeMapping),
                            args.First().Type,
                            args.First().TypeMapping),
                        args.First().Type,
                        args.First().TypeMapping));

    }

    // 使用特性标记方法要映射到返回标量值的数据库函数
    // 在查询中调用则会翻译为对数据库函数的调用
    // 在数据库中也要确保定义了匹配的函数
    [DbFunction]
    public static int MyFunction(int value)
    {
        return value % 2;
    }

}
